package lib.util;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import object.Book;

public class BookOperator {

	// Here defines how to connector the database.
	private static final String connectionString = "jdbc:mysql://localhost:3306/library";
	// username for mysql, 'root' by default.
	private static final String dbUsername = "root";
	// user password for mysql, change to yours.
	private static final String dbPassword = "admin";
	public static final int PAGESIZE = 20;
	public static int lastNum = 0;

	static {
		try {
			// class name for mysql driver
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static ArrayList<Book> searchByName(String book_name, int page,
			String user_id) throws SQLException {
		String query = "SELECT * FROM books natural join book_specify WHERE book_name = ? && book_exist = true "
				+ "limit " + (page - 1) * PAGESIZE + "," + page * PAGESIZE;
		String queryNum = "SELECT count(*) FROM books natural join book_specify WHERE book_name = ? && book_exist = true";

		Connection c = DriverManager.getConnection(connectionString,
				dbUsername, dbPassword);
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<Book> books = new ArrayList<Book>();

		try {
			// prepare the statement according to the SQL statement.
			ps = c.prepareStatement(queryNum);
			ps.setString(1, book_name);
			rs = ps.executeQuery();
			rs.next();
			lastNum = rs.getInt(1);

			ps = c.prepareStatement(query);
			ps.setString(1, book_name);
			// Execute the Query
			rs = ps.executeQuery();

			while (rs.next()) {
				Book book = new Book(book_name, rs.getString("writer"),
						rs.getString("publisher"), rs.getDate("publish_date"),
						rs.getString("book_code"), rs.getString("type"),
						rs.getBoolean("only_teach"), rs.getInt("shelf_num"),
						rs.getInt("total_book_num"),
						rs.getBoolean("book_exist"),
						rs.getBoolean("book_on_shelf"),
						rs.getString("library_name"), rs.getString("location"),
						isReservedBy(rs.getString("book_id"), user_id),
						rs.getString("book_id"));
				books.add(book);
			}
			return books;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// Notice! Always close the connection after using.
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (c != null) {
				c.close();
			}
		}
		return null;
	}

	public static ArrayList<Book> searchByCode(String book_code, int page,
			String user_id) throws SQLException {
		String query = "SELECT * FROM books natural join book_specify WHERE book_code = ? && book_exist = true "
				+ "limit " + (page - 1) * PAGESIZE + "," + page * PAGESIZE;
		String queryNum = "SELECT count(*) FROM books natural join book_specify WHERE book_code = ? && book_exist = true";

		Connection c = DriverManager.getConnection(connectionString,
				dbUsername, dbPassword);
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<Book> books = new ArrayList<Book>();

		try {
			// prepare the statement according to the SQL statement.
			ps = c.prepareStatement(queryNum);
			ps.setString(1, book_code);
			rs = ps.executeQuery();
			rs.next();
			lastNum = rs.getInt(1);

			ps = c.prepareStatement(query);
			ps.setString(1, book_code);
			// Execute the Query
			rs = ps.executeQuery();

			while (rs.next()) {
				Book book = new Book(rs.getString("book_name"),
						rs.getString("writer"), rs.getString("publisher"),
						rs.getDate("publish_date"), rs.getString("book_code"),
						rs.getString("type"), rs.getBoolean("only_teach"),
						rs.getInt("shelf_num"), rs.getInt("total_book_num"),
						rs.getBoolean("book_exist"),
						rs.getBoolean("book_on_shelf"),
						rs.getString("library_name"), rs.getString("location"),
						isReservedBy(rs.getString("book_id"), user_id),
						rs.getString("book_id"));
				books.add(book);
			}
			return books;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// Notice! Always close the connection after using.
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (c != null) {
				c.close();
			}
		}
		return null;
	}

	public static boolean isReservedBy(String book_id, String user_id)
			throws SQLException {
		String query = "SELECT count(*) FROM reserve WHERE book_id = ? && user_id = ? && reserve_valid = true";

		Connection c = DriverManager.getConnection(connectionString,
				dbUsername, dbPassword);
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			// prepare the statement according to the SQL statement.
			ps = c.prepareStatement(query);
			ps.setString(1, book_id);
			ps.setString(2, user_id);
			rs = ps.executeQuery();
			rs.next();
			return rs.getInt(1) == 1;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// Notice! Always close the connection after using.
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (c != null) {
				c.close();
			}
		}
		return false;
	}

	public static void insertBookSheetA(String book_code, String type,
			String book_name, String writer, String publisher,
			Date publish_date, int shelf_num) {
		

	}
	
	public static void insertBookSheetB(String book_id, String book_code, String location) {
		
	}
	public static void deleteBookSheetA(String book_code){
		
	}
	public static void deleteBookSheetB(String book_id,String book_code,String location){
		
	}
}
